#!/usr/bin/env python
# -*- encoding: utf-8 -*-
# Created on 2018-10-19 14:39:07

import mysql.connector
from six import itervalues
from datetime import date
from datetime import datetime
from datetime import timedelta
from .config import mysql_config

class SQL:
    connection = ''
    connect = True
    placeholder = '%s'

    def __init__(self):
        if self.connect:
            SQL.connect(self)

    def escape(self,string):
        return '`%s`' % string

    def connect(self):
        config = {
            'user': mysql_config.get('user', 'root'),
            'password': mysql_config.get('password', '123456'),
            'password': mysql_config.get('password', '123456'),
            'host': mysql_config.get('host', '127.0.0.1'),
            'database': mysql_config.get('database', 'db_name'),
            'charset': mysql_config.get('charset', 'utf8'),
        }
        try:
            cnx = mysql.connector.connect(**config)
            SQL.connection = cnx
            return True
        except mysql.connector.Error as err:
            if (err.errno == errorcode.ER_ACCESS_DENIED_ERROR):
                print("The credentials you provided are not correct.")
            elif (err.errno == errorcode.ER_BAD_DB_ERROR):
                print("The database you provided does not exist.")
            else:
                print("Something went wrong: " , err)
            return False

    def update(self, tablename, where, values):
        if SQL.connection == '':
            print("Please connect first")
            return False
        if not values:
            print("Empty values")
            return False
        if not where:
            print("Empty where")
            return False
        tablename = self.escape(tablename)
        set_vals = []
        for key in values.keys():
            item = "%s='%s'" % (self.escape(key), str(values[key]),)
            set_vals.append(item)
        sql_query = "UPDATE %s SET %s WHERE %s" % (tablename, ", ".join(set_vals), where)
        # print(sql_query)
        # return True
        cur = SQL.connection.cursor()
        try:
            cur.execute(sql_query)
            SQL.connection.commit()
            return True
        except mysql.connector.Error as err:
            print ("An error occured: {}".format(err))
            return False

    def replace(self,tablename=None, values=None):
        if SQL.connection == '':
            print("Please connect first")
            return False
        tablename = self.escape(tablename)
        if values:
            _keys = ", ".join(self.escape(k) for k in values)
            _values = ", ".join([self.placeholder, ] * len(values))
            sql_query = "REPLACE INTO %s (%s) VALUES (%s)" % (tablename, _keys, _values)
        else:
            sql_query = "REPLACE INTO %s DEFAULT VALUES" % tablename

        cur = SQL.connection.cursor()
        try:
            if values:
                cur.execute(sql_query, list(itervalues(values)))
            else:
                cur.execute(sql_query)
            SQL.connection.commit()
            return True
        except mysql.connector.Error as err:
            print ("An error occured: {}".format(err))
            return False
